package com.ssi.mrms.dao.Impl;

import static java.text.MessageFormat.format;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.ssi.mrms.dao.ManageProjectDao;
import com.ssi.mrms.dao.mapping.ManageProjectMapping;
import com.ssi.mrms.model.DataTableModel;
import com.ssi.mrms.model.ManageProjectModel;

@Repository
public class ManageProjectDaoImpl extends BaseDaoImpl<ManageProjectModel> implements ManageProjectDao{
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	@Override
	public boolean save(final ManageProjectModel model) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT), Statement.RETURN_GENERATED_KEYS);
				int i = 0;
				ps.setInt(++i, model.getProjectId());
				ps.setInt(++i, model.getAccSiteAccId());
				ps.setString(++i, model.getProjectName());
				ps.setString(++i, model.getProjectNickname());
				ps.setDate(++i, new java.sql.Date(model.getCreateDate().getTime()));
				ps.setString(++i, model.getCreateUser());
				ps.setDate(++i, new java.sql.Date(model.getUpdateDate().getTime()));
				ps.setString(++i, model.getUpdateUser());
				return ps;
			}
		}, keyHolder);
		// int id = keyHolder.getKey().intValue();
		return true;
	}

	@Override
	public boolean delete(Integer id) {
		int row = jdbcTemplate.update(format(SQL_DELETE_BY_ID), new Object[] { id });
		return row > 0;
	}

	@Override
	public ManageProjectModel findByID(Integer id) {
		ManageProjectModel model = null;
		List<ManageProjectModel> lists = jdbcTemplate.query(format(SQL_QUERY_BY_ID), new Object[] { id },
				new ManageProjectMapping());
		if (lists != null && !lists.isEmpty()) {
			model = lists.get(0);
		}
		return model;
	}

	@Override
	public List<ManageProjectModel> findALL() {
		return jdbcTemplate.query(format(SQL_QUERY), new ManageProjectMapping());
	}

	@Override
	public boolean update(ManageProjectModel model) {
		int row = jdbcTemplate.update(format(SQL_UPDATE),
				new Object[] { 
			model.getProjectId(), 
			model.getAccSiteAccId(),
			model.getProjectName(),
			model.getProjectNickname(),
			model.getUpdateDate(), 
			model.getUpdateUser(), 
			model.getId() 
			});
		return row > 0;
	}
	
	private static final String TABLE_NAME = "manage_project";
	private static final String SQL_QUERY = " select * from manage_project";
	private static final String SQL_QUERY_BY_ID = " select * from manage_project where id = ? ";
	private static final String SQL_INSERT = " insert into manage_project (project_id, ACC_SITE_Acc_id, project_name, project_nickname, create_date, create_by, update_date, update_by) values (?,?,?,?,?,?,?,?)";
	private static final String SQL_DELETE_BY_ID = " delete from manage_project where id = ? ";
	private static final String SQL_UPDATE = " update manage_project set project_id = ?, ACC_SITE_Acc_id = ?, project_name = ?, project_nickname = ?, update_date = ?, update_by = ?  where id = ? ";


	@Override
	public DataTableModel<ManageProjectModel> limit(SQLWhere[] sqlWheres,
			String[] sqlOrderBys, int page, int perPage) {
		return super.limit(TABLE_NAME, sqlWheres, sqlOrderBys, page, perPage, jdbcTemplate, new ManageProjectMapping());
	}

}
